// tableJobs.js
class JobsTable {
    constructor(app, db) {
        this.app = app;
        this.db = db;
    }

    init() {
        const sqlTable = `
        CREATE TABLE IF NOT EXISTS jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            input TEXT,
            output TEXT DEFAULT '',
            config TEXT DEFAULT '',
            status TEXT DEFAULT '',
            current_node TEXT DEFAULT NULL,
            current_task INTEGER DEFAULT NULL,
            error TEXT DEFAULT NULL,
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )`;

        this.db.run(sqlTable, (err) => {
            if (err) {
                console.error('Create "jobs" Table Failed, Error: ', err);
            }
        });
    }

    async insert(jobInfo) {
        return new Promise((resolve, reject) => {
            const { input, config } = jobInfo;

            const sqlInsert = `
                INSERT INTO jobs (input, config, status, create_time, update_time)
                VALUES (?, ?, ?, datetime(CURRENT_TIMESTAMP, '+8 hour') , datetime(CURRENT_TIMESTAMP, '+8 hour'));`;
            this.db.run(sqlInsert, [input, config, 'pending'], function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });
        });
    }

    async update(jobInfo) {
        // console.log(`update jobInfo = `, jobInfo);
        return new Promise(async (resolve, reject) => {
            let sqlUpdateSQL = 'UPDATE jobs SET';
            const params = [];

            // update field if exists output, error, status, current_node, current_task
            if (jobInfo.output !== null && jobInfo.output !== undefined) {
                sqlUpdateSQL += ' output = ?,';
                params.push(jobInfo.output);
            }

            if (jobInfo.error !== null && jobInfo.error !== undefined) {
                sqlUpdateSQL += ' error = ?,';
                params.push(jobInfo.error);
            }

            if (jobInfo.status !== null && jobInfo.status !== undefined) {
                sqlUpdateSQL += ' status = ?,';
                params.push(jobInfo.status);
            }

            if (jobInfo.current_node !== null && jobInfo.current_node !== undefined) {
                sqlUpdateSQL += ' current_node = ?,';
                params.push(jobInfo.current_node);
            }

            if (jobInfo.current_task !== null && jobInfo.current_task !== undefined) {
                sqlUpdateSQL += ' current_task = ?,';
                params.push(jobInfo.current_task);
            }

            // Remove the trailing comma and add the WHERE clause
            sqlUpdateSQL += " update_time = datetime(CURRENT_TIMESTAMP, '+8 hour') WHERE id = ?;";
            params.push(jobInfo.id);

            // Execute the UPDATE SQL statement
            this.db.run(sqlUpdateSQL, params, (err) => {
                if (err) {
                    console.error('Error updating tasks:', err);
                    reject({ code: -1, msg: err.message });
                }
                resolve({ code: 0, msg: 'Updated OK' });
            });
        });
    }

    async delete(jobId) {
        const deleteStatement = `
            DELETE FROM jobs WHERE id = ?;
        `;

        return new Promise((resolve, reject) => {
            this.db.run(deleteStatement, jobId, (err) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(true);
                }
            });
        });
    }

    async get(jobId) {
        return new Promise((resolve, reject) => {
            const sqlQuery = `SELECT * FROM jobs${jobId ? ' WHERE id = ?' : ''};`;
            const params = jobId ? [jobId] : [];

            this.db.all(sqlQuery, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }
}

module.exports = { JobsTable };
